General Assembly Final Project: A Winning Four Stock Portfolio

by:

Matthew Q. Rubino, CFA

----Project Description----

---Data---

I leverage three data sets to complete this project:
(1) Data Set 1: Four Excel Files Containing Daily Data
Using Bloomberg's API functionality in Microsoft Excel, I have retrieved four stocks daily price data metrics including volume, open price, close price, high price, and low price on a daily basis since the beginning of 2008. The four stocks I chose include Wix.com, Goldman Sachs, General Motors, and Etsy.
(2) Data Set 2: Four Excel Files Containing Annual Fundamental Data
Using Bloomberg's API functionality in Microsoft Excel, I have retrieved four stocks’annual fundamental performance data including sales, operating margins, dividends paid, shares bought back, and free cash flow yield. The four stocks I chose include Wix.com, Goldman Sachs, General Motors, and Etsy.
(3) Data Set 3: Daily Price Data Metrics of the Walt Disney Company
Using Yahoo Finance's API functionality, I have run code to retrieve the Walt Disney Company's daily price data metrics including high, low, open, close, volume, and adjusted close from January 2012 to December 2020.

Loading Each of the Four Company's Logos

---Goals---

(1) Data Set 1: With this data the plan is to complete a data science project whereby I explore the change in each of the four stock’s price over time, daily average returns, moving averages, correlations between the stocks, volatility of the daily average returns, and a variety of portfolio statistics. The goal is to suggest (or not suggest) that these five stocks represent an interesting portfolio of stocks from a portfolio construction perspective. The plan is to use a variety of charts help validate or unvalidate the thesis. As Albert Einstein once said, "A picture is worth a thousand words."

(3) Data Set 3: With this data, the goal is to create a model using machine learning to try to predict the Walt Disney Company's closing price one day in the future by training the model with 100 days of prior daily closing price information.

----Findings----

---What are some of the basic price data statistics of each of the companies?
WIX: The mean daily return of WIX is 0.14% and the standard deviation of daily returns is 2.58%. The largest daily return in the data set is 25.32% while the smallest is -21.78%. A return in the top 75th percentile begins at 0.90%.
GS: The mean daily return of GS is 0.03% and its standard deviation is 2.01%. The largest daily price return in the dataset is 26.47%. The smallest daily price return in the dataset is -18.96%. The 75th Percentile of Daily Price returns for GS begins at 0.54%.
GM: The mean daily return of GM is 0.03% and the standard deviation of its daily returns is 1.73%. The largest daily price return in the data set is 19.94%. The smallest daily price return in the data set is -17.32%. The 75th percentile of daily price returns begins at 0.53%.
ETSY: The mean daily return of ETSY is 0.18% and the standard deviation of its daily returns is 3.67%. The largest daily price return in the dataset is 87.50%. The smallest daily price return in the dataset is -28.34%. The 75th percentile of daily price returns begins at 1.09%.

--- How do each of the four stock’s daily price returns correlate with each other?

I show a correlation heatmap of each of the four stocks. We see from the chart that the four stocks exhibit low positive correlation with each other, with the exception of Goldman Sachs and General Motors. The pairwise correlations range from 0.16 (General Motors and Etsy) to 0.59 (Goldman Sachs and General Motors).

---Which of the four stocks are currently in a golden cross pattern? Which of the stocks had the largest number of periods in a golden cross pattern?

Only Wix.com is currently trading in a golden cross pattern. Goldman Sachs had the largest number of periods in a golden cross pattern; however, the longevity of Wix.com’s and Etsy’s golden cross trading patterns tended to be longer then the likes of Goldman Sachs and General Motors.

---Which of the four companies experienced the most operating margin growth and exhibited expansion in every period?

---Which of the four companies experienced the most volatile annual free cash flow yield?

Goldman Sachs; Etsy’s and WIX’s were generally consistently positive, but were the most slim free cash flow yields. General Motors’ free cash flow yield was consistently positive and ranged from nearly 5% to more than 20%.

---On initial indications, does the portfolio of four stocks look interesting?

Yes; as shown, in the chart just prior to the heading, “Predicting the Next Closing Price of Disney”, the portfolio significantly dampened the risk of two the most volatile underlying stocks – Etsy and Wix.com. By using the equal weight portfolio, we have reduced the daily return standard deviation to nearly 1.60%. As a base for comparison, Wix.com and Etsy’s daily volatility were 2.58% and 3.67%. In addition, with respect the pairwise correlations of each of the underlying companies’ operating fundamental, the correlation matrices show that the business together offer good levels of diversification. Pairwise correlations generally exhibit low positive correlation and in some cases are negatively correlated.

---What is the created model’s predicted next day closing price of Disney?

167.04759 (USD); this seems unlikely given yesterday's close of 176.47 (USD). The model will need to be trained further through tweaks of the amount of layers as well as other factors to ensure better accuracy.

Begin by Importing the Necessary Libraries:

Below is the List of Stocks I Perform Analysis on:

Read the Following Four Data Spreadsheets:

Join Four Data Spreadsheets into one Dataframe

Check the Top 5 Entries of the New Dataframe

Check the Top 5 Entries of the New Dataframe

The Data Looks Good; Now I Will Clean the Data:

First, I Will Drop Null Values

I Will Now Analyze the High Level Results of Each of the Four Stocks by Locating Respective Keys

WIX High Level Results:

GS High Level Results

GM High Level Results:

ETSY High Level Results

Below is the General Information of the Etsy; I am Looking to Identify the Data Types:

Plotting a Historical View of the Closing Prices of Each of the Stocks

First, let's make it easy to show the data we wish to plot by creating names and using the loc function to identify keys in the data set

Now, let's create our chart:

Let's plot a historical view of the volume of each of the stocks

Again, let's make it easy to show the data we wish to plot by creating names and using the loc function to identify keys in the data set

Now, I will Create the Chart:

It is Difficult to Read the Actual Data so I Will Show Each Stock's Volume Separately

WIX's Volume:

GS' Volume

GM's Volume

ETSY's Volume

Now, I will Calculate Daily Price Returns Using the Closing Prices of Each of the Stocks; I have Labelled the New Column as Returns

Now I will show JointPlots of each of the Pairs of Stocks to Show How Their Daily Price Returns Covary Together

I will begin be renaming the ways to access the keys from the main dataframe to keep the code clean

I will compare WIX to itself as a test; this should output a perfectly linear relationship

Now I Will Create a JointPlot of WIX vs. GS

First, I will re-read the underlying data and clean it with dropna

Now I will rename so as to have easy access to the columns

Creating the Joint plot for WIX vs. GS

Repeating the Steps for WIX vs. GM

Repeating the Steps for WIX vs. ETSY

Repeating the Steps for GS vs. GM

Repeat the Steps for GS vs. ETSY

Repeating the Steps for GM vs. ETSY

Now I Will Create a Chart Showing WIX's Historical Daily Price Returns

Re-reading and Cleaning the Data

Creating New Names so Columns are Easy to Access

Creating the Chart:

Now, I will Create a Hitogram of WIX's Daily Price Returns to Show their Distribution

Re-Reading and Cleaning

Renaming Variables and Setting Bins for the Historgram

Creating the Histogram

Writing Code to reset the Bins to Attain a Higher Level of Information

Attaining High Level Statistics on WIX:

Takeaways:

The mean daily return of WIX is 0.14% and the standard deviation of daily returns is 2.58%
The largest daily return in the data set is 25.32% while the smallest is -21.78%
A return in the top 75th percentile begins at 0.90%

Now I will repeat this same process for the other three stocks: GS, GM, and ETSY

----GS----

Takeaways:

The mean daily return of GS is 0.03% and its standard deviation is 2.01%
The largest daily price return in the dataset is 26.47%
The smallest daily price return in the dataset is -18.96%
The 75th Percentile of Daily Price returns for GS begins at 0.54%

----GM----

Takeaways:

The mean daily return of GM is 0.03% and the standard deviation of its daily returns is 1.73%
The largest daily price return in the data set is 19.94%
The smallest daily price return in the data set is -17.32%
The 75th percentile of daily price returns begins at 0.53%

----ETSY----

Takeaways:

The mean daily return of ETSY is 0.18% and the standard deviation of its daily returns is 3.67%
The largest daily price return in the dataset is 87.50%
The smallest daily price return in the dataset is -28.34%
The 75th percentile of daily price returns begins at 1.09%

Let's Plot a Correlation Matrix of the Daily Price Returns of Each of the Four Stocks

Re-Reading the Data

Combining all of the Price Change Columns into one DataFrame

Let's Create a Chart Showing the Expected Risk and Return of the Four Stocks

----Technical Analysis----

Now, I will calculate the 50, 100, and 200 Moving Average Daily Price Returns and Add them to the main dataframe

Renaming for the three new moving averages for WIX

Plotting the Daily MAs

Now, I would like to identify days where a "golden cross has occured"

Definition in my own words: The golden cross is a technical statistic whereby the 50D MA Crosses over the 200D; it is a bullish signal for the going forward price of a particular stock.

Investopedia Definition: The golden cross is a chart pattern that is a bullish signal in which a relatively short-term moving average crosses above a long-term moving average. The golden cross is a bullish breakout pattern formed from a crossover involving a security's short-term moving average (such as the 15-day moving average) breaking above its long-term moving average (such as the 50-day moving average) or resistance level. As long-term indicators carry more weight, the golden cross indicates a bull market on the horizon and is reinforced by high trading volumes.

Investopedia Link: https://www.investopedia.com/terms/g/goldencross.asp

Calculating the Golden Cross

Checking to make sure the column was added

Renaming

Creating a function to check for Golden Cross Periods

Checking

Creating a Chart to Show Where Golden Crosses Occur

Renaming:

Charting:

Repeating the same process for the other three stocks

----GS----

----GM----

----ETSY----

----Fundamental Data Analysis----

Reading the Following Four Spreadsheets

Creating a new dataframe that has only of Trailing 12M Sales for each of the stocks

Removing columns that are not needed

Checking the Concatenated Results of the Sales dataframe:

Creating an Seaborn LinePlot of Trailing 12M Sales for GS and GM

Creating an Seaborn LinePlot of Trailing 12M Sales for WIX and ETSY

Re-reading the Data

Create a new dataframe of operating margin growth

Checking the new concatenated results

Creating a Scatter Plot of Annual Operating Margin % Change of WIX, GS, GM, and ETSY

Re-Reading the Data

Create a new dataframe of FCF Yield

Creating Free Cash Flow Yield Stem Plots

----WIX----

----GS----

----GM----

----ETSY----

Running Correlation Matrices of the Fundamentals

Creating a correlation Matrix of T12 Sales of Each of the Four Companies

Creating a correlation Matrix of the % Change in Operating Margin for the Four Companies

Creating a correlation Matrix of Annual Free Cash Flow Yield % of Each of the Four Companies

Management Team Information

Adding a Picture of the CEO of Each of the Companies

WIX.com: Avishai Abrahami

Avishai is an experienced technology entrepreneur and developer who has grown Wix.com from a start-up in 2006 to the world’s leading do-it-yourself web publishing platform. Avishai and his co-founders, developed the Wix platform as a result of their frustration with the complexities of website creation. Six years on, they’re helping tens of millions of users with no development skills to create beautiful and professional websites. Avishai is a true technology entrepreneur. Prior to founding Wix in 2006, Avishai led several successful start-ups as founder/senior manager, including the software company AIT, which he founded in 1994 and sold in 1997.

Goldman Sachs: David Solomon

David Solomon is Chairman and Chief Executive Officer and a member of the Board of Directors of The Goldman Sachs Group, Inc. Previously, he was President and Chief Operating Officer and prior to that, he served as Co-Head of the Investment Banking Division from 2006 to 2016. Before that, Mr. Solomon was Global Head of the Financing Group, which includes all capital markets and derivative products for the firm’s corporate clients. He joined Goldman Sachs as a Partner in 1999. Mr. Solomon is a member of the Board of Trustees of Hamilton College and serves on the board of The Robin Hood Foundation.

General Motors: Mary Barra

Mary Barra is Chair and Chief Executive Officer of General Motors. She was elected Chair of the GM Board of Directors on Jan. 4, 2016, and has served as CEO of GM since Jan. 15, 2014. Under Barra’s leadership, GM envisions a world with zero crashes, to save lives; zero emissions, so future generations can inherit a healthier planet; and zero congestion, so customers get back a precious commodity – time. She is focused on improving the customer experience and strengthening GM’s core vehicle and services business, while also working to lead the transformation of personal mobility through advanced technologies like connectivity, electrification and autonomous driving. Prior to becoming CEO, Barra served as GM executive vice president, Global Product Development, Purchasing and Supply Chain since August 2013, and as senior vice president, Global Product Development since February 2011. In these roles, Barra and her teams were responsible for the design, engineering and quality of GM vehicle launches worldwide. Previously, she served as vice president, Global Human Resources; vice president, Global Manufacturing Engineering; plant manager, Detroit Hamtramck Assembly; and in several other executive engineering and staff positions. Barra began her career with GM in 1980 as a General Motors Institute (Kettering University) co-op student at the Pontiac Motor Division. She graduated with a Bachelor of Science degree in electrical engineering in 1985, followed by a Master of Business Administration from the Stanford Graduate School of Business in 1990. Barra serves on the Board of Directors of the Walt Disney Company, the Duke University Board of Trustees and the Detroit Economic Club. Additionally, she serves as the Chair and founding member of GM’s Inclusion Advisory Board. She is also a member of The Business Council and a Board Member of the Business Roundtable, where she chairs the Education and Workforce Committee and a subcommittee by the same name for the Special Board Committee on Racial Equity and Justice.

Etsy: Josh Silverman

Josh Silverman is Etsy's Chief Executive Officer, leading the company as it builds a platform that empowers creative entrepreneurs around the world. He is also a member of Etsy's board of directors. Josh's two decades of leadership experience include growing consumer technology companies and scaling global marketplaces. He previously served as President of Consumer Products and Services at American Express, CEO of Skype, and CEO of shopping.com, and he held various executive roles at eBay. Earlier in his career, Josh co-founded Evite, Inc. where he also served as the company's CEO. He is currently on the board of directors of Shake Shack. Josh has a deep commitment to community and civic engagement. He currently serves as Chairman of Code Nation, a non-profit that equips students in under-resourced schools with both fundamental coding skills and professional experiences. He was previously a member of the Stanford Business School Management board and the Lincoln Center Theater board.

Proposed Portfolio of 4 Stocks

Creating a new dataframe to show expected return and risk

Creating Columns that Hold Proposed Weights of Each of the Four Stocks

Creating a histogram of daily portfolio returns

Creating a new dataframe of only returns

Creating a Risk vs. Return Chart with the Proposed Portfolio in Comparison to the Stocks in Isolation

Predicting the Next Day Closing Price of Disney

Plotting the Closing Prices of Disney

Importing More Libraries

Prepare Data for Neural Network; Scaling Down Closing Price Data so it Fits Between 0 and 1

Deciding on the number of days to look back on to help predict future prices

Preparing the Training Data:

Convert to Numpy Arrays

Reshape x-train so it works with the neural network

Building the Model

Let's see how the model would perform with the data we have: TESTING

Loading the test data:

Now we need to get the prices, scale the dataset, and concatenate the full dataset that we want to predict on:

Make Some Predictions on Test Data

Plot the Test Predictions

Predict Next Day

Bibliography

“Stock Price Prediction Using Python & Machine Learning.” YouTube, YouTube, 21 Dec. 2019, www.youtube.com/watch?v=QIUxPv5PJOY.

krishnaik06. “Stock Price Prediction And Forecasting Using Stacked LSTM- Deep Learning.” YouTube, YouTube, 25 May 2020, www.youtube.com/watch?v=H6du_pfuznE.

“Stock Price Prediction Project with TensorFlow Keras ❌Make Money Using Keras LSTM Neural Networks.” YouTube, YouTube, 16 Aug. 2020, www.youtube.com/watch?v=dKBKNOn3gCE&t=724s.

“Walt Disney Company (The) (DIS) Stock Historical Prices & Data.” Yahoo! Finance, Yahoo!, 27 May 2021, finance.yahoo.com/quote/DIS/history?p=DIS.

“Bloomberg's API Data Functionality.” Bloomberg, API.

General Assembly Logo. General Assembly, coursereport-s3-production.global.ssl.fastly.net/uploads/school/logo/2/original/CMYK-Red_SmallGeneralAssembly-Cog__1.png.

Wix.com Logo. Wix.com, logo.stocklight.com/NASDAQ/WIX_original.png.

Goldman Sachs Logo. Goldman Sachs, media2.vault.com/20642/goldmansachs_logo_2018.jpg.

General Motors Logo. General Motors, ewscripps.brightspotcdn.com/dims4/default/8a37e6a/2147483647/strip/true/crop/2690x1513+0+135/resize/1280x720!/quality/90/?url=http%3A%2F%2Fewscripps-brightspot.s3.amazonaws.com%2Fb2%2F15%2F8e74683743db90685e003ba76b87%2Fscreen-shot-2021-01-08-at-12.45.29%20PM.png.

Etsy Logo. Etsy, www.statisticbrain.com/wp-content/uploads/2015/06/etsy-company-website-statistics-e1473352717799.jpg.

The Walt Disney Company Logo. The Walt Disney Company, cdn1.parksmedia.wdprapps.disney.com/media/blog/wp-content/uploads/2017/08/askh4769895.png.